from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template


def query_hld(ref_date, tick_list=None, portf_list=None):
	
	#---avg cost(local,base)
	#---mkt price(local,base)		
	#---mkt value(local,base)	
	#---mkt exposure
	#---cost exposure
	#---return
	
	sql_tpl = Template('''
	  select hld.*
		from (select dp.lticker as sec_ticker,
					 si."name" as sec_name,
					 dp.security_type as sec_type,
					 dp.amount as sec_position,
					 dp.avg_cost_price as cost_local,
					 dp.avg_cost_price / fx.price as cost_base,
					 dp.mkt_price as price_local,
					 dp.mkt_price / fx.price as price_base,
					 dp.amount * dp.mkt_price * dp.point_value as val_local,
					 dp.amount * dp.mkt_price * dp.point_value / fx.price as val_base,
					 dp.amount * dp.avg_cost_price * dp.point_value / fx.price /
					 ave.av as cost_exposure,
					 dp.amount * dp.mkt_price * dp.point_value / fx.price /
					 ave.av as mkt_exposure,
					 sign(dp.amount) * (dp.mkt_price / dp.avg_cost_price - 1) as ret,
					 0.01 * ave.av / (dp.mkt_price * dp.point_value / fx.price) as one_pct_num,
					 dp.price_currency as local_curncy,
					 pi.currency as base_curncy,
					 dp.portfolio_id as portfolio_id,
					 pi."name" as portf_name
				from mkt_expo dp
				left join comm_security_static_info si on si.ticker = dp.lticker
				left join portfolio_info pi on pi.portfolio_id = dp.portfolio_id
				left join comm_fx_latest fx on fx.ref_date = dp.ref_date
										   and fx.dom_curncy = dp.price_currency
										   and fx.for_curncy = pi.currency
				left join asset_value_ex@accounting ave on ave.ref_date =
														   dp.ref_date
													   and ave.entity =
														   dp.portfolio_id
													   and ave.type = 'NAV'
			   where dp.ref_date = TO_DATE('${DATE}', 'yyyy-mm-dd')
				 and ${PORTF_CRITERIA}
				 and ${TICK_CRITERIA}) hld
		left join security_type_rank r on r.sec_type = hld.sec_type
	   order by hld.portfolio_id, r.rank, hld.val_base desc
	''')
	
	sql_text = sql_tpl.substitute(DATE=ref_date.isoformat(), 
								TICK_CRITERIA=format_sql_criteria('dp.lticker', tick_list), 
								PORTF_CRITERIA=format_sql_criteria('dp.portfolio_id', portf_list))	
								
	#with open('D:/work/pke/testcases/dump.log', 'w') as log_file:
	#	log_file.write(sql_text)
	
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()
	